package cn.hnc.checkapi.repository;

import cn.hnc.checkapi.bean.Product;
import java.util.List;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

public interface ProductRepository extends JpaRepository<Product,Long>, JpaSpecificationExecutor<Product> {
   @Query(
      value = " SELECT \n    *\nFROM\n    CheckData.product\nWHERE\n    partCheckFlag = 0 AND macNO <> ''  and macNo <> '0'  and id >= ?1 and id < ?2  group by macNo  ",
      nativeQuery = true
   )
   List find(Long startId, Long endId);

   Product findFirstByMacSN(String macSN);

   List findByIdIn(List productIds);

   List findByMacSN(String macSN);

   List findAll();

   @Query(
      value = "select * from product p  where if(ISNULL(?1) || length(trim(?1)) < 1 || -1 = ?1 , 1 = 1, p.uploadTime >= ?1) and if(ISNULL(?2) || length(trim(?2)) < 1 || -1 = ?2, 1 = 1, p.uploadTime <= ?2) and if(ISNULL(?3) || length(trim(?3)) < 1, 1 = 1, p.customerName like %?3%) and if(ISNULL(?4) || length(trim(?4)) < 1, 1 = 1, p.contractCode like %?4%) and if(ISNULL(?5) || length(trim(?5)) < 1, 1 = 1, p.macSN like %?5%) and if(ISNULL(?6) || length(trim(?6)) < 1, 1 = 1, p.ipcCode like %?6%) and  if(ISNULL(?7) || length(trim(?7)) < 1, 1 = 1, p.macNO like %?7%) ",
      nativeQuery = true
   )
   Page selProduct(Integer beginTime, Integer endTime, String customerName, String contractCode, String macSN, String ipcCode, String macNO, Pageable pageable);

   @Query(
      value = " select  p.* from product p join (SELECT distinct a.productId FROM axis a   where     if(ISNULL(:servoId) || length(trim(:servoId)) < 1, 1 = 1, a.servoId like CONCAT('%',:servoId,'%')) and  if(ISNULL(:servoModel) || length(trim(:servoModel)) < 1, 1 = 1, a.servoModel like CONCAT('%',:servoModel,'%')) and  if(ISNULL(:elecId) || length(trim(:elecId)) < 1, 1 = 1, a.elecId like CONCAT('%',:elecId,'%')) and  if(ISNULL(:elecModel) || length(trim(:elecModel)) < 1, 1 = 1, a.elecModel like CONCAT('%',:elecModel,'%'))   ) result  on p.id = result.productId  where  if(ISNULL(:beginTime) || length(trim(:beginTime)) < 1 || -1 = :beginTime , 1 = 1, p.uploadTime >= :beginTime ) and  if(ISNULL(:endTime) || length(trim(:endTime)) < 1 || -1 = :endTime, 1 = 1, p.uploadTime <= :endTime ) and  if(ISNULL(:customerName) || length(trim(:customerName)) < 1, 1 = 1, p.customerName like CONCAT('%',:customerName,'%') ) and  if(ISNULL(:contractCode) || length(trim(:contractCode)) < 1, 1 = 1, p.contractCode like CONCAT('%',:contractCode,'%') )  and  if(ISNULL(:macSN) || length(trim(:macSN)) < 1, 1 = 1, p.macSN like  CONCAT('%',:macSN,'%')) and  if(ISNULL(:ipcCode) || length(trim(:ipcCode)) < 1, 1 = 1, p.ipcCode like CONCAT('%',:ipcCode,'%')) and  if(ISNULL(:macNO) || length(trim(:macNO)) < 1, 1 = 1, p.macNO like CONCAT('%',:macNO,'%'))   ",
      countQuery = " select    count(*) from product p join (SELECT distinct a.productId FROM axis a   where     if(ISNULL(:servoId) || length(trim(:servoId)) < 1, 1 = 1, a.servoId like CONCAT('%',:servoId,'%')) and  if(ISNULL(:servoModel) || length(trim(:servoModel)) < 1, 1 = 1, a.servoModel like CONCAT('%',:servoModel,'%')) and  if(ISNULL(:elecId) || length(trim(:elecId)) < 1, 1 = 1, a.elecId like CONCAT('%',:elecId,'%')) and  if(ISNULL(:elecModel) || length(trim(:elecModel)) < 1, 1 = 1, a.elecModel like CONCAT('%',:elecModel,'%'))   ) result  on p.id = result.productId  where  if(ISNULL(:beginTime) || length(trim(:beginTime)) < 1 || -1 = :beginTime , 1 = 1, p.uploadTime >= :beginTime ) and  if(ISNULL(:endTime) || length(trim(:endTime)) < 1 || -1 = :endTime, 1 = 1, p.uploadTime <= :endTime ) and  if(ISNULL(:customerName) || length(trim(:customerName)) < 1, 1 = 1, p.customerName like CONCAT('%',:customerName,'%') ) and  if(ISNULL(:contractCode) || length(trim(:contractCode)) < 1, 1 = 1, p.contractCode like CONCAT('%',:contractCode,'%') )  and  if(ISNULL(:macSN) || length(trim(:macSN)) < 1, 1 = 1, p.macSN like  CONCAT('%',:macSN,'%')) and  if(ISNULL(:ipcCode) || length(trim(:ipcCode)) < 1, 1 = 1, p.ipcCode like CONCAT('%',:ipcCode,'%')) and  if(ISNULL(:macNO) || length(trim(:macNO)) < 1, 1 = 1, p.macNO like CONCAT('%',:macNO,'%'))   ",
      nativeQuery = true
   )
   Page selProduct(Integer beginTime, Integer endTime, String customerName, String contractCode, String macSN, String ipcCode, String macNO, String servoId, String servoModel, String elecId, String elecModel, Pageable pageable);
}
